package com.eorchis.module.user.dao.require;

import org.springframework.stereotype.Repository;

import com.eorchis.core.annotation.EscapeProperty;
import com.eorchis.core.basedao.condition.BaseCondition;
import com.eorchis.core.page.commond.SortInfoBean;
import com.eorchis.module.department.domain.Department;
import com.eorchis.module.department.domain.DepartmentUser;
import com.eorchis.module.user.domain.User;
import com.eorchis.module.user.domain.UserInfoCondition;
import com.eorchis.utils.utils.PropertyUtil;

/** @deprecated */
@Repository("com.eorchis.module.user.dao.require.UserInfoRequire")
public class UserInfoRequire {
	public UserInfoCondition queryCheckUserLoginHQL(UserInfoCondition condition) {
		StringBuffer result = new StringBuffer("from User u where 1=1 ");

		if ((condition.getSearchLoginID() != null) && (!"".equals(condition.getSearchLoginID()))) {
			result.append(" and u.loginID = ?");
			condition.addParameter(condition.getSearchLoginID());
		}
		if ((condition.getSearchPassWord() != null) && (!"".equals(condition.getSearchPassWord()))) {
			result.append(" and u.password = ?");
			condition.addParameter(condition.getSearchPassWord());
		}
		if ((condition.getSearchActiveState() != null) && (!"".equals(condition.getSearchActiveState()))) {
			result.append(" and u.activeState = ?");
			condition.addParameter(User.IS_ACTIVE_Y);
		}
		condition.setSql(result.toString());
		return condition;
	}

	public UserInfoCondition currentDepartmentHQL(UserInfoCondition condition) {
		StringBuffer result = new StringBuffer("select dep from DepartmentUser dul join dul.department dep join dul.user u where u.userId = ?");
		condition.addParameter(condition.getSearchUserId());
		result.append(" and dul.activeState = ?");
		condition.addParameter(DepartmentUser.IS_ACTIVE_Y);
		result.append(" and dep.activeState = ?");
		condition.addParameter(Department.IS_ACTIVE_Y);
		result.append(" and u.activeState = ?");
		condition.addParameter(User.IS_ACTIVE_Y);
		condition.setSql(result.toString());
		return condition;
	}

	public UserInfoCondition currentManageScopeHQL(UserInfoCondition condition) {
		StringBuffer result = new StringBuffer("select dep from UserManageScope ums join ums.department dep join ums.user u where u.userId = ?");
		condition.addParameter(condition.getSearchUserId());
		result.append(" and dep.activeState = ?");
		condition.addParameter(Department.IS_ACTIVE_Y);
		result.append(" and u.activeState = ?");
		condition.addParameter(User.IS_ACTIVE_Y);
		condition.setSql(result.toString());
		return condition;
	}

	public UserInfoCondition listRoleByUserId(String searchUserId) {
		UserInfoCondition condition = new UserInfoCondition();
		String sql = "select ru.role.roleID as roleId,ru.role.roleName as roleName,ru.user.userId as userId from RoleUser ru where ru.user.userId = ? ";
		condition.addParameter(searchUserId);
		condition.setSql(sql);
		return condition;
	}

	@EscapeProperty(escapePropertyNames = "searchLoginID,searchUserName")
	public UserInfoCondition listUser(UserInfoCondition condition) {
		/*String hql = "";
		hql = hql + "select u.userId as userId ,";
		hql = hql + " u.loginID as loginID ,";

		hql = hql + " u.password as password ,";
		hql = hql + " u.userName as userName ,";
		hql = hql + " u.birthday as birthday ,";
		hql = hql + " u.phone as phone ,";
		hql = hql + " u.mobileTelephone as mobileTelephone ,";
		hql = hql + " u.email as email ,";
		hql = hql + " u.paperNum as paperNum ,";
		hql = hql + " u.hierarchy as hierarchy ,";
		hql = hql + " u.dutyRemark as dutyRemark ,";
		hql = hql + " u.isZugong as zuGong ,";
		hql = hql + " u.examState as examState ,";
		hql = hql + " u.isBureauReserve as isBureauReserve ,";
		hql = hql + " u.currentDutyDate as currentDutyTime ,";
		hql = hql + " u.isShiguan as shiGuan ,";
		hql = hql + " u.activeState as activeState ,";
		hql = hql + " u.orderNum as orderNum ,";
		hql = hql + " u.availabilityState as availabilityState ,";
		hql = hql + " u.officeHoldingDate as officeholdingDate ,";
		hql = hql + " u.isDangwu as dangWu ,";
		hql = hql + " u.isTongzhan as tongZhan ,";
		hql = hql + " u.viewState as viewState ,";
		hql = hql + " u.guid as guid ,";
		hql = hql + " u.remark as remark ,";
		hql = hql + " u.operateTime as operateTime ,";
		hql = hql + " u.isTemp as isTemp ";

		if ((condition.getSearchDepID() != null) && (!condition.getSearchDepID().equals(""))) {
			hql = hql + ", du.department.deptID as deptID ";
			hql = hql + " from DepartmentUser du, User u";
			hql = hql + " where  du.user.userId = u.userId and du.department.deptID = ? ";

			condition.addParameter(condition.getSearchDepID());
		} else {
			hql = hql + " FROM User u where 1=1 ";
		}

		if ((condition.getSearchUserId() != null) && (!"".equals(condition.getSearchUserId()))) {
			hql = hql + " AND u.userId = ? ";
			condition.addParameter(new String(condition.getSearchUserId()));
		}
		if ((condition.getSearchLoginID() != null) && (!"".equals(condition.getSearchLoginID()))) {
			hql = hql + " AND u.loginID like ? escape '/'";
			condition.addParameter("%" + condition.getSearchLoginID() + "%");
		}
		if ((condition.getSearchUserName() != null) && (!"".equals(condition.getSearchUserName()))) {
			hql = hql + " AND u.userName like ? escape '/'";
			condition.addParameter("%" + condition.getSearchUserName() + "%");
		}
		if ((condition.getSearchActiveState() != null) && (!"".equals(condition.getSearchActiveState()))) {
			hql = hql + " AND u.activeState = ? ";
			condition.addParameter(new Integer(condition.getSearchActiveState()));
		}
		SortInfoBean sortInfo = condition.getSortInfo();
		if (sortInfo != null)
			hql = hql + " ORDER BY " + sortInfo.getProperty() + " " + sortInfo.getDirection();
		else {
			hql = hql + " ORDER BY u.orderNum";
		}
		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer
				.append(" SELECT t.USERID as \"userId\",t.LOGIN_ID as \"loginID\",t.SEX_CODE as \"sexCode\",t.PASSWORD as \"password\",t.USER_NAME as \"userName\", t.BIRTHDAY as \"birthday\",t.PHONE as \"phone\",t.MOBILE_TELEPHONE as \"mobileTelephone\", t.EMAIL as \"email\",t.PAPER_NUM as \"paperNum\", t.HIERARCHY as \"hierarchy\",t.DUTY_REMARK as \"dutyRemark\",t.ZUGONG as \"zuGong\",t.EXAM_STATE as \"examState\", t.IS_BUREAU_RESERVE as \"isBureauReserve\",t.CURRENT_DUTY_TIME as \"currentDutyTime\",t.SHIGUAN as \"shiGuan\", t.ACTIVE_STATE as \"activeState\",t.ORDER_NUM as \"orderNum\",t.AVAILABILITY_STATE as \"availabilityState\", t.OFFICEHOLDING_DATE as \"officeholdingDate\",t.DANGWU as \"dangWu\",t.TONGZHAN as \"tongZhan\", t.VIEW_STATE as \"viewState\",t.GUID as \"guid\",t.REMARK as \"remark\", (select wmsys.wm_concat(br.role_name) from base_role_user bru, base_role br where br.role_id = bru.role_id  \tand bru.userid = t.userid group by bru.userid) as \"roleNames\", (select wmsys.wm_concat(br.role_id) from base_role_user bru, base_role br where br.role_id = bru.role_id  \tand bru.userid = t.userid group by bru.userid) as \"roleIds\" ");

		condition.setSql(hql);
		*/

		StringBuffer sql = new StringBuffer();
		sql.append("select u.USERID as \"userId\", u.LOGIN_ID as \"loginID\", u.USER_NAME as \"userName\", u.SEX_CODE as \"sexCode\", " +
				"floor(MONTHS_BETWEEN(sysdate, u.birthday)/12) as \"age\", b.DATA_NAME as \"folkCode\", u.DUTY_REMARK as \"dutyRemark\", " +
				"u.MOBILE_TELEPHONE as \"mobileTelephone\", u.REMARK as \"remark\", " +
				"to_char(u.OPERATE_TIME,'yyyy-mm-dd') as \"operateTime\", " +
				"(case when Extract(year from u.operate_time) >= Extract(year from sysdate) then 2 else 1 end) as \"isOperate\" ");
		if (PropertyUtil.objectNotEmpty(condition.getSearchDepID())) {
			sql.append(", du.DEPID as \"deptID\" from BASE_DEPARTMENT_USER du, BASE_USER u left join BASE_BASEDATA b on u.folk_code = b.data_code " +
					"where du.USERID = u.USERID and du.DEPID = ?");
			condition.addParameter(condition.getSearchDepID());
		} else
			sql.append(" from BASE_USER u left join BASE_BASEDATA b on u.folk_code = b.data_code where 1 = 1 ");
		if (PropertyUtil.objectNotEmpty(condition.getSearchUserId())) {
			sql.append(" and u.USERID = ?");
			condition.addParameter(new String(condition.getSearchUserId()));
		}
		if (PropertyUtil.objectNotEmpty(condition.getSearchLoginID())) {
			sql.append(" and u.LOGIN_ID like ? escape '/'");
			condition.addParameter("%" + condition.getSearchLoginID() + "%");
		}
		if (PropertyUtil.objectNotEmpty(condition.getSearchUserName())) {
			sql.append(" and u.USER_NAME like ? escape '/'");
			condition.addParameter("%" + condition.getSearchUserName() + "%");
		}
		if (PropertyUtil.objectNotEmpty(condition.getSearchActiveState())) {
			sql.append(" and u.ACTIVE_STATE = ? ");
			condition.addParameter(new Integer(condition.getSearchActiveState()));
		}
		SortInfoBean sortInfo = condition.getSortInfo();
		if (PropertyUtil.objectNotEmpty(sortInfo))
			sql.append(" order by " + sortInfo.getProperty() + " " + sortInfo.getDirection());
		else
			sql.append(" order by u.ORDER_NUM");
		condition.setSql(sql.toString());
		return condition;
	}

	@EscapeProperty(escapePropertyNames = "searchLoginID,searchUserName")
	public UserInfoCondition countUserHql(UserInfoCondition condition) {
		StringBuffer hqlBuffer = new StringBuffer();
		hqlBuffer.append(" SELECT COUNT(*) from User t");
		if ((condition.getSearchDepID() != null) && (!condition.getSearchDepID().equals(""))) {
			hqlBuffer.append(",DepartmentUser du WHERE du.user.userId = t.userId AND  du.department.deptID = ?");
			condition.addParameter(condition.getSearchDepID());
		} else {
			hqlBuffer.append(" WHERE 1=1");
		}

		if ((condition.getSearchUserId() != null) && (!"".equals(condition.getSearchUserId()))) {
			hqlBuffer.append(" AND t.userId = ? ");
			condition.addParameter(new String(condition.getSearchUserId()));
		}
		if ((condition.getSearchLoginID() != null) && (!"".equals(condition.getSearchLoginID()))) {
			hqlBuffer.append(" AND t.loginID like ? escape '/'");
			condition.addParameter("%" + condition.getSearchLoginID() + "%");
		}
		if ((condition.getSearchUserName() != null) && (!"".equals(condition.getSearchUserName()))) {
			hqlBuffer.append(" AND t.userName like ? escape '/'");
			condition.addParameter("%" + condition.getSearchUserName() + "%");
		}
		if ((condition.getSearchActiveState() != null) && (!"".equals(condition.getSearchActiveState()))) {
			hqlBuffer.append(" AND t.activeState = ? ");
			condition.addParameter(new Integer(condition.getSearchActiveState()));
		}
		condition.setSql(hqlBuffer.toString());
		return condition;
	}

	public UserInfoCondition discardOrReuseUserHQL(UserInfoCondition condition) {
		String hql = "update User bd set bd.activeState = ? where 1=1";
		condition.addParameter(new Integer(condition.getSearchIsEnabled()));
		if ((condition.getSearchUserIds() != null) && (condition.getSearchUserIds().length > 0)) {
			hql = hql + " and bd.userId in (" + condition.assemblyParameterListSQL(condition.getSearchUserIds()) + ")";
			condition.addParameterList(condition.getSearchUserIds());
		}
		condition.setSql(hql);
		return condition;
	}

	public BaseCondition getUserListByIDArrayHQL(String[] idArray) {
		BaseCondition condition = new BaseCondition();
		condition.setSql(" from User where userId in (" + condition.assemblyParameterListSQL(idArray) + ")");
		condition.addParameterList(idArray);
		return condition;
	}

	public String getUserByIDHQL() {
		return "from User where userId = :userId";
	}
}